package com.supermap.dataservice.user.dao;

import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.common.entity.Area;
import com.supermap.dubbo.api.user.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @Author:langhongshuai
 * @Description:用户数据访问层
 * @Date:18/1/3 上午10:42
 */
@Mapper
public interface UserDao {

    /***
     * 插入关联id
     * **/
    @Insert("insert into vip_user_relation (vip_id,user_id) values(#{vipId},#{userId})")
    int insertVipUserRelation(@Param("vipId") int vipId,@Param("userId")int userId);

    /***
     * 查询用户id
     * **/
    @Select("select * from t_user where  del_flg=0 and mobile=#{mobile}")
    User queryVipUser(@Param("mobile") String mobile);

    //    查询VIP用户
    @Select("select * from t_vip where userId=#{userId}")
    public Vip getVip(@Param("userId") Integer userId);

    @SelectProvider(type = UserDao.VipUserEmeeySql.class, method = "getVipUserListSql")
    public List<User> getVipUserList(@Param("table") Table table, @Param("user") User user, @Param("vipId") int vipId);

    /**
     * 获取用户列表总数
     *
     * @param table
     * @return
     */
    @SelectProvider(type = UserDao.VipUserEmeeySql.class, method = "getUserListCountSql")
    public int getVipUserListCount(@Param("table") Table table, @Param("user") User user, @Param("vipId") int vipId);

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class VipUserEmeeySql {

        //按条件分页查询用户列表
        public String getVipUserListSql(Map<String, Object> map) {

            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
//            获取登陆用户id
            int vipId = (int) map.get("vipId");
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT t1.*, t2.name AS rolename,t3.areaname\n" +
                    " FROM t_user t1\n" +
                    " RIGHT  join vip_user_relation t4 on t1.id = t4.user_id " +
                    " LEFT JOIN t_role t2 ON t1.rolecode = t2. CODE\n" +
                    " left join t_area t3 on t1.areaCode = t3.areacode\n");
            sb.append(" where true\n");
            sb.append(" and t1.del_flg=0\n");
            if (vipId > 0) {
                sb.append(" and t4.vip_id = " + vipId + "\n");
            }
            if (user.getUsername() != null) {
                sb.append(" and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sb.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sb.append("and t1.areacode like '%" + user.getAreaCode() + "%'\n");
            }
            sb.append("group by id\n");
            sb.append("order by id asc limit\n" + table.getStartPoint() + "," + table.getLimit());
            sb.append(";");
            return sb.toString();
        }

        //按条件查询用户列表总数
        public String getUserListCountSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            //            获取登陆用户id
            int vipId = (int) map.get("vipId");

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT count(*) as count\n" +
                    " FROM t_user t1\n" +
                    " RIGHT  join vip_user_relation t4 on t1.id = t4.user_id " +
                    " LEFT JOIN t_role t2 ON t1.rolecode = t2. CODE\n" +
                    " left join t_area t3 on t1.areaCode = t3.areacode\n");
            sql.append(" where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (vipId > 0) {
                sql.append(" and t4.vip_id = " + vipId + "\n");
            }
            if (user.getUsername() != null) {
                sql.append(" and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sql.append(" and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sql.append(" and t1.areacode like '%" + user.getAreaCode() + "%'\n");
            }
            if (user.getRolecode() != null) {
                sql.append(" and t2.code like '%" + user.getRolecode() + "%'\n");
            }
            sql.append(";");
            return sql.toString();
        }
    }

    /*****************************************************/

    /******
     * 查询用户名是否已绑定
     * ****/
    @Select("select * from bind_user where userId=#{userId}")
    BindUser queryBindUserName(@Param("userId") String userId);

    /***
     * 查询绑定用户名
     * ****/
    @Select("select count(1) from bind_user where loginName=#{loginName} and password=#{password}")
    public int queryBindUser(@Param("loginName") String loginName, @Param("password") String password);

    /****
     * 用户绑定
     * ***/
    @Insert("Insert into bind_user (userId,loginName,password) values(#{userId},#{loginName},#{password})")
    public void insertBindUser(@Param("userId")String userId,@Param("loginName") String loginName, @Param("password") String password);

    /**
     * 通过用户id逻辑删除用户
     *
     * @param userId
     */
    @Delete("update t_user set del_flg=1 where id=#{userId}")
    public void updateStatus(@Param("userId") int userId);

    /**
     * 通过用户名重置密码
     *
     * @param userId
     * @param newPassword
     */
    @Select("update t_user set password =#{newPassword} where id=#{userId}")
    public void resetPasswordCQ(@Param("userId") int userId, @Param("newPassword") String newPassword);

    /***
     * 根据userid查询用户
     * ****/
    @Select("select * from t_user where id = #{userId}")
    User queryUser(@Param("userId") int userId);

    /***
     * 获取角色信息
     * ***/
    @Select("SELECT * FROM `t_role`")
    List<Role> getRole();

    /***
     * 获取区域信息
     * ***/
    @Select("SELECT * FROM `t_area` where areacode like '50%'")
    List<Area> getAreaInfo();

    /***
     * 查询电话和登录名是否存在
     * ***/
    @Select("select count(*) from t_user where loginName = #{loginName} and del_flg=0")
    int queryLoginName(@Param("loginName") String loginName);

    /***
     * 查询电话号码是否存在
     * **/
    @Select("select count(*) from t_user where  del_flg=0 and mobile=#{mobile}")
    int queryMobile(@Param("mobile") String mobile);

    /**
     * 重庆增加用户
     *
     * @param user
     */
    @Insert("Insert into t_user(username,password,loginName,showName,address,email,mobile,Introduction,areaCode,rolecode,latitude,longitude,del_flg,createtime) " +
            "values(#{user.username},#{user.password},#{user.loginName},#{user.showName},#{user.address},#{user.Email},#{user.mobile},#{user.Introduction},#{user.areaCode},#{user.rolecode},#{user.latitude},#{user.longitude},0,#{user.createtime})")
    public int insertCQ(@Param("user") User user);


    /**
     * 通过用户名和密码获取用户信息
     *
     * @param userName
     * @param password
     * @return
     */
    @Select("select * from t_user t1 where t1.loginName=#{userName} and t1.password=#{password}")
    public User checkUserByUP(@Param("userName") String userName, @Param("password") String password);

    /**
     * 通过用户名重置密码
     *
     * @param userName
     * @param newPassword
     */
    @Select("update t_user set password =#{newPassword} where username=#{userName}")
    public void resetPassword(@Param("userName") String userName, @Param("newPassword") String newPassword);

    /**
     * 通过（帐号或手机号）和密码登陆
     *
     * @param userName
     * @param password
     * @return
     */
    @Select("select * from t_user t1 where (t1.loginName =#{userName} or t1.mobile =#{userName})and t1.password=#{password}")
    public User Login(@Param("userName") String userName, @Param("password") String password);

    /**
     * 全国APP注册
     *
     * @param user
     */
    @InsertProvider(type = UserSql.class, method = "register")
    public void register(@Param("user") User user);

    /**
     * 通过账户名查找用户
     *
     * @param account
     * @return
     */
    @Select("Select * from t_user t1 where t1.loginName =#{account} or t1.mobile =#{account}")
    public List<User> checkMobileAndLoginName(@Param("account") String account);

    /**
     * 增加用户
     *
     * @param u
     */
    @Insert("Insert into t_user(username,password,loginName,mobile,areaCode,latitude,longitude) " +
            "values(#{username},#{password},#{loginName},#{mobile},#{areaCode},#{latitude},#{longitude})")
    public void insert(User u);

    /**
     * 通过登录名查找用户
     *
     * @param loginName
     * @return
     */
    @Select("select id,username from t_user where loginName=#{loginName} ")
    public User find(@Param("loginName") String loginName);

    /**
     * 通过登录名查找用户
     *
     * @param id
     * @return
     */
    @Select("select t1.* from t_user t1 LEFT JOIN t_role t2 on t1.rolecode=t2.`code` where t1.id=#{id} ")
    public User findUserById(@Param("id") String id);

    /**
     * 通过区域code和角色获取用户信息
     *
     * @param areaCode
     * @param roleId
     * @return
     */
    @Select("select t1.id,t1.username,t1.address,t1.HeadImage,t1.Introduction from t_user t1 \n" +
            "INNER JOIN t_user_role t2\n" +
            "on t1.id=t2.userid\n" +
            "where \n" +
            "IF(ISNULL(#{areaCode}),true,t1.areaCode=#{areaCode} and t2.roleid=#{roleId});")
    public List<User> findUserByAreaRId(@Param("areaCode") String areaCode, @Param("roleId") int roleId);

    /**
     * 修改用户信息
     *
     * @param user
     * @return
     */
    @UpdateProvider(type = UserSql.class, method = "updateUserById_sql")
    int updateUserById(@Param("user") User user);

    /**
     * 获取增加用户后的id
     *
     * @return
     */
    @Select("Select max(id) as id from t_user")
    int selectId();

    /**
     * 分页显示用户列表
     *
     * @param table
     * @return
     */
    @SelectProvider(type = UserSql.class, method = "getUserListSql")
    public List<User> getUserList(@Param("table") Table table, @Param("user") User user);

    /***
     * App分页显示用户列表
     * **/
    @SelectProvider(type = UserSql.class, method = "getAppUserListSql")
    public List<User> getAppUserList(@Param("table") Table table, @Param("user") User user);
    /**
     * 获取用户列表总数AppApp
     *
     * @param table
     * @return
     */
    @SelectProvider(type = UserSql.class, method = "getUserListCountSql")
    public int getUserListCount(@Param("table") Table table, @Param("user") User user);

    /**
     * 模糊查询
     *
     * @param keyWord
     * @return
     */
    @Select("SELECT t1.*\n" +
            "FROM t_user t1\n" +
            "WHERE t1.showName LIKE #{keyWord}\n" +
            "ORDER BY id\n" +
            "LIMIT #{page},#{count}")
    List<User> searchUserInfoByKeyWord(@Param("keyWord") String keyWord, @Param("page") int page, @Param("count") int count);

    /**
     * VIP用户搜索
     *
     * @param showName   用户名
     * @param type
     * @param areaCode
     * @param beginCount 起始搜索位置
     * @param count      搜索条数
     * @return
     */
    @Select("select v.*,v.Lat as latitude, v.Lon as longitude from t_vip v \n" +
            "JOIN t_user u on v.userId = u.id \n" +
            "JOIN t_user_role ur on ur.userid = u.id \n" +
            "JOIN t_role r on r.id = ur.roleid \n" +
            "where v.`name` like #{showName} \n" +
            "and if(isnull(#{areaCode}),true,v.areaCode like #{areaCode}) \n" +
            "and if(isnull(#{type}),true,r.code = #{type})\n" +
            "order by v.id \n" +
            "limit #{beginCount},#{count}")
    List<Vip> searchVip(String showName, String type, String areaCode, int beginCount, int count);

    /**
     * 修改用户信息
     *
     * @param user
     * @return
     */
    @UpdateProvider(type = UserSql.class,
            method = "updateUser")
    int updateUser(@Param("user") User user);

    /**
     * 通过用户id删除用户
     *
     * @param userId
     */
    @Delete("delete * from t_user where id=#{userId}")
    public void delUserById(@Param("userId") int userId);

    /**
     * 申请成为VIP
     *
     * @param vip
     * @return
     */
    @InsertProvider(type = VipSql.class,
            method = "applyForVIP")
    int applyForVIP(@Param("vip") Vip vip);

    @Select("select * from t_vip_apply where userid=#{id}")
    Vip findApplyVipById(@Param("id") String id);

    /**
     * 成为VIP
     *
     * @param vip
     * @return
     */
    @InsertProvider(type = VipSql.class,
            method = "affirmToVIP")
    int affirmToVIP(Vip vip);

    @Update("update t_vip_apply set del_flg=0 where userid=#{id}")
    int applyVIPFlag(@Param("id") String userId);

    @Select("select * from t_vip where userId=#{userId}")
    Vip findVipById(@Param("userId") String userId);

    class VipSql {
        public String applyForVIP(Map<String, Object> map) {
            Vip vip = (Vip) map.get("vip");
            String table = "t_vip_apply";
            return getVipInsertSql(vip, table);
        }

        public String affirmToVIP(Map<String, Object> map) {
            Vip vip = (Vip) map.get("vip");
            String table = "t_vip";
            return getVipInsertSql(vip, table);
        }

        private String getVipInsertSql(Vip vip, String table) {
            StringBuilder sql = new StringBuilder();
            sql.append("insert into `").append(table).append("` ( `userId`, `name`, `type`, `CountyName`, `BelongsTownship`, `BelongsVillage`, `areaCode`, `Lon`, `Lat`, `height`, `BusinessScope`, `AreaScale`, `AnnualProductionValue`, `ProdOperaCount`, `TechCount`, `TechAverageAge`, `NetComputerAccess`, `useSmartPhones`, `SmartPhoneBrand`, `LeaderName`, `LeaderPhone`, `TechLeaderName`, `TechLeaderPhone`, `intro`, `epath`, `longitude`, `latitude`, `addr`, `del_flg`, `createtime`) values ( \n")
                    .append("'").append(vip.getUserId()).append("',")
                    .append("'").append(vip.getName()).append("',")
                    .append("'").append(vip.getType()).append("',")
                    .append("'").append(vip.getCountyName()).append("',")
                    .append("'").append(vip.getBelongsTownship()).append("',")
                    .append("'").append(vip.getBelongsVillage()).append("',")
                    .append("'").append(vip.getAreaCode()).append("',")
                    .append("'").append(vip.getLon()).append("',")
                    .append("'").append(vip.getLat()).append("',")
                    .append("'").append(vip.getHeight()).append("',")
                    .append("'").append(vip.getBusinessScope()).append("',")
                    .append("'").append(vip.getAreaScale()).append("',")
                    .append("'").append(vip.getAnnualProductionValue()).append("',")
                    .append("'").append(vip.getProdOperaCount()).append("',")
                    .append("'").append(vip.getTechCount()).append("',")
                    .append("'").append(vip.getTechAverageAge()).append("',")
                    .append("'").append(vip.getNetComputerAccess()).append("',")
                    .append("'").append(vip.getUseSmartPhones()).append("',")
                    .append("'").append(vip.getSmartPhoneBrand()).append("',")
                    .append("'").append(vip.getLeaderName()).append("',")
                    .append("'").append(vip.getLeaderPhone()).append("',")
                    .append("'").append(vip.getTechLeaderName()).append("',")
                    .append("'").append(vip.getTechLeaderPhone()).append("',")
                    .append("'").append(vip.getIntro()).append("',")
                    .append("'").append(vip.getEpath()).append("',")
                    .append("'").append(vip.getLongitude()).append("',")
                    .append("'").append(vip.getLatitude()).append("',")
                    .append("'").append(vip.getAddr()).append("',")
                    .append("'").append(vip.getDel_flg()).append("',")
                    .append("'").append(vip.getCreatetime()).append("',")
                    .append("\t);");
            return sql.toString();
        }
    }

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class UserSql {
        public String updateUserById_sql(Map<String, Object> map) {
            User user = (User) map.get("user");
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE t_user SET ");
            if(user.getUsername()!=null){
                sql.append(" userName = " + "'" + user.getUsername() + "',");
            }
            if(user.getShowName()!=null) {
                sql.append(" showName = " + "'" + user.getShowName() + "',");
            }
            if(user.getLoginName()!=null) {
                sql.append(" loginName = " + "'" + user.getLoginName() + "',");
            }
            if(user.getMobile()!=null) {
                sql.append(" mobile = " + "'" + user.getMobile() + "',");
            }
            if(user.getAddress()!=null) {
                sql.append(" address = " + "'" + user.getAddress() + "',");
            }
            if(user.getAreaCode()!=null) {
                sql.append(" areaCode = " + "'" + user.getAreaCode() + "',");
            }
            if(user.getIntroduction()!=null) {
                sql.append(" Introduction = " + "'" + user.getIntroduction() + "',");
            }
            if(user.getEmail()!=null) {
                sql.append(" Email = " + "'" + user.getEmail() + "',");
            }
            sql.append(" HeadImage = " + "'" + user.getHeadImage() + "'");
//            sql.append("latitude = " + "'"+user.getLatitude()+"'");
//            sql.append("longitude = " + "'"+user.getLongitude()+"'");
            sql.append(" WHERE id = " + user.getId());
            return sql.toString();
        }

        //按条件分页查询用户列表
        public String getUserListSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT t1.*, t2.name AS rolename,t3.areaname\n" +
                    "FROM t_user t1\n" +
                    "LEFT JOIN t_role t2 ON t1.rolecode = t2. CODE\n" +
                    "left join t_area t3 on t1.areaCode = t3.areacode\n");
            sql.append("where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (user.getUsername() != null) {
                sql.append("and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sql.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sql.append("and t1.areacode like '" + user.getAreaCode() + "%'\n");
            }else{
                sql.append("and t1.areacode like '" + 50 + "%'\n");
            }
            if (user.getRolecode() != null) {
                sql.append("and t1.rolecode like '%" + user.getRolecode() + "%'\n");
            }
            sql.append("group by id\n");
            sql.append("order by id asc limit\n" + table.getStartPoint() + "," + table.getLimit());
            sql.append(";");
            return sql.toString();
        }

        //App按条件分页查询用户列表
        public String getAppUserListSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT t1.*, t2.name AS rolename,t3.areaname\n" +
                    "FROM t_user t1\n" +
                    "LEFT JOIN t_role t2 ON t1.rolecode = t2. CODE\n" +
                    "left join t_area t3 on t1.areaCode = t3.areacode\n");
            sql.append("where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (user.getUsername() != null) {
                sql.append("and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sql.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sql.append("and t1.areacode like '%" + user.getAreaCode() + "%'\n");
            }
            if (user.getRolecode() != null) {
                sql.append("and t1.rolecode like '%" + user.getRolecode() + "%'\n");
            }
            sql.append("group by id\n");
            sql.append("order by id asc limit\n" + table.getStarAppPoint() + "," + table.getLimit());
            sql.append(";");
            return sql.toString();
        }

        //按条件查询用户列表总数
        public String getUserListCountSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT count(*) as count\n" +
                    "FROM t_user t1\n" +
                    "LEFT JOIN t_role t2 ON t1.rolecode = t2. CODE\n" +
                    "left join t_area t3 on t1.areaCode = t3.areacode\n");
            sql.append("where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (user.getUsername() != null) {
                sql.append("and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sql.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sql.append("and t1.areacode like '" + user.getAreaCode() + "%'\n");
            }else{
                sql.append("and t1.areacode like '" + 50 + "%'\n");
            }
            if (user.getRolecode() != null) {
                sql.append("and t2.code like '%" + user.getRolecode() + "%'\n");
            }
            sql.append(";");
            return sql.toString();
        }

        public String updateUser(Map<String, Object> map) {
            User user = (User) map.get("user");
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE t_user SET ");
            sql.append(" showName = " + "'" + user.getShowName() + "',");
            sql.append(" loginName = " + "'" + user.getLoginName() + "',");
            sql.append(" mobile = " + "'" + user.getMobile() + "',");
            sql.append(" address = " + "'" + user.getAddress() + "',");
            sql.append(" areaCode = " + "'" + user.getAreaCode() + "',");
            sql.append(" Introduction = " + "'" + user.getIntroduction() + "',");
            sql.append(" HeadImage = " + "'" + user.getHeadImage() + "',");
            sql.append(" Email = " + "'" + user.getEmail() + "',");
            sql.append(" latitude = " + "'" + user.getLatitude() + "',");
            sql.append(" longitude = " + "'" + user.getLongitude() + "',");
            sql.append(" rolecode = " + "'" + user.getRolecode() + "'");
            sql.append(" WHERE id = " + user.getId());
            return sql.toString();
        }

        public String register(Map<String, Object> map) {
            User user = (User) map.get("user");
            StringBuilder sql = new StringBuilder();
            sql.append("Insert into t_user(username,password,loginName,mobile,areaCode,rolecode,latitude,longitude");
            if (user.getAddress() != null) {
                sql.append(",address");
            }
            sql.append(") values(");
            sql.append("'").append(user.getUsername()).append("'").append(",");
            sql.append("'").append(user.getPassword()).append("'").append(",");
            sql.append("'").append(user.getLoginName()).append("'").append(",");
            sql.append("'").append(user.getMobile()).append("'").append(",");
            sql.append("'").append(user.getAreaCode()).append("'").append(",");
            sql.append("'").append(user.getRolecode()).append("'").append(",");
            sql.append("'").append(user.getLatitude()).append("'").append(",");
            sql.append("'").append(user.getLongitude()).append("'");
            if (user.getAddress() == null) {
                sql.append(");");
            } else {
                sql.append(",").append("'").append(user.getAddress()).append("'").append(");");
            }
            return sql.toString();
        }

    }
}
